---
title: Raw SQL
description: A raw SQL operation runs arbitrary SQL against the database.
---

<Warning>
  `pgroll` is unable to guarantee that raw SQL migrations are safe and will not
  result in application downtime.
</Warning>

## Structure

This is intended as an 'escape hatch' to allow a migration to perform operations that are otherwise not supported by `pgroll`.

<YamlJsonTabs>
```yaml
sql:
  up: SQL expression
  down: SQL expression
```
```json
{
  "sql": {
    "up": "SQL expression",
    "down": "SQL expression"
  }
}
```
</YamlJsonTabs>

By default, a `sql` operation cannot run together with other operations in the same migration. This is to ensure pgroll can correctly track the state of the database. However, it is possible to run a `sql` operation together with other operations by setting the `onComplete` flag to `true`.

The `onComplete` flag will make this operation run the `up` expression on the complete phase (instead of the default, which is to run it on the start phase).

`onComplete` flag is incompatible with `down` expression, as `pgroll` does not support running rollback after complete was executed.

<YamlJsonTabs>
```yaml
sql:
  up: SQL expression
  onComplete: true
```
```json
{
  "sql": {
    "up": "SQL expression",
    "onComplete": true
  }
}
```
</YamlJsonTabs>


<Warning>
  The `down` migration must be idempotent. When an `up` migration fails, `pgroll` automatically runs the corresponding `down` migration to clean up leftover objects. If the `down` migration is not idempotent (does not contain `IF EXISTS`), the rollback will fail.
</Warning>

## Examples

### Create a table with a raw SQL migration

A raw SQL migration to create a table:

<ExampleSnippet example="05_sql.yaml" languange="yaml" />

### Run a SQL migration on migration complete

A raw SQL migration run on migration completion rather than start.

<ExampleSnippet example="32_sql_on_complete.yaml" languange="yaml" />
